from flask import (
    Blueprint, flash, g, redirect, render_template, request, url_for
)
from werkzeug.exceptions import abort
from typing import List, Tuple
import sqlite3

from forumAPP.auth import login_required
from forumAPP.database import get_database_connection
from forumAPP.comment_tree import get_parent_comments, get_subtree_comments

bp = Blueprint('comment', __name__, url_prefix='/comment')


def get_article_author(commentID: int) -> sqlite3.Row:
    """ 根据commentID, 寻找原文、文章作者

    :return: 仅包含原文章、文章作者信息的查询结果
    """
    conn = get_database_connection()
    SQL_code: str = '''
    SELECT c.articleID, a.last_modified_time, 
        a.authorID, a.title, a.body, u.nickname
    FROM comment AS c, article AS a, user AS u
    WHERE c.articleID=a.numberID 
        AND a.authorID=u.numberID
        AND c.numberID=?;
    '''

    article_author = conn.execute(SQL_code, (commentID,)).fetchone()
    return article_author


def get_article_comments_subcomments_commentator(articleID: int) -> Tuple[sqlite3.Row]:
    """ 返回文章下的所有根评论、子评论；评论者信息以及父评论者信息

    """
    conn = get_database_connection()
    # 根评论
    SQL_code: str = '''
    SELECT c.numberID,c.prevCommentID, c.authorID, c.articleID,
        c.last_modified_time, c.content, u.nickname
    FROM comment AS c, user AS u
    WHERE c.authorID=u.numberID
        AND c.articleID=?
        AND c.prevCommentID<0;
    '''

    article_comments = conn.execute(
        SQL_code, (articleID,)).fetchall()

    SQL_code = '''
    SELECT c.numberID,c.authorID, c.articleID, c.prevCommentID,
        c.last_modified_time, c.content, u.nickname,
        u1.nickname AS parent_nickname
    FROM comment AS c, user AS u, comment AS c1, user AS u1
    WHERE c.authorID=u.numberID
        AND c1.authorID=u1.numberID
        AND c.prevCommentID=c1.numberID
        AND c.prevCommentID>0
        AND c.articleID=?;
    '''
    article_comments.extend(conn.execute(SQL_code, (articleID,)).fetchall())

    return tuple(article_comments)


@bp.route('/<int:ID>', methods=('GET', 'POST',))
def detail(ID: int):
    commentID: int = ID
    # 1. 查原文及原作者信息
    article_author = get_article_author(ID)
    articleID: int = article_author['articleID']

    # 2. 查文章下的所有评论 (一级评论&子级评论)
    article_comments = get_article_comments_subcomments_commentator(articleID)

    tree_comments = get_subtree_comments(article_comments, commentID)
    tree_comments = tree_comments[1:]

    parent_comments = get_parent_comments(article_comments, commentID)
    num_tree_comments = len(tree_comments)
    num_parent_comments = len(parent_comments)

    return render_template('/comment/detail.html',
                           tree_comments=tree_comments,
                           parent_comments=parent_comments,
                           article_author=article_author,
                           num_tree_comments=num_tree_comments,
                           num_parent_comments=num_parent_comments)


@bp.route('/create', methods=('GET', 'POST',))
@login_required
def create():
    prevID: int = request.args.get('prevID')
    prevID = int(prevID)
    if prevID is None:
        return f"<h1>不可独立新建评论,合法示例:{url_for('comment.create',prevID=13)}</h1>"

    conn = get_database_connection()
    # 1.找到(原评论prevID)原文章&作者
    prev_article_author = get_article_author(prevID)
    articleID: int = prev_article_author['articleID']

    if request.method == "GET":
        # 找原文所有评论(含评论发表者)
        article_comments = get_article_comments_subcomments_commentator(
            articleID)

        parent_comments = get_parent_comments(article_comments, prevID)
        num_parent_comments = len(parent_comments)
        return render_template('/comment/create.html',
                               prev_article_author=prev_article_author,
                               parent_comments=parent_comments,
                               num_parent_comments=num_parent_comments)

    # POST用户已提交表单
    print('新建评论,用户输入:', request.form)
    content: str = request.form.get('body')
    SQL_code: str = '''
    INSERT INTO comment
            (authorID, articleID,prevCommentID,last_modified_time,content)
    VALUES  (?,         ?,              ?,    CURRENT_TIMESTAMP, ?);
    '''
    conn.execute(SQL_code, (g.user['numberID'], articleID, prevID, content,))
    conn.commit()
    return redirect(url_for('comment.detail', ID=prevID))
